﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SQLite;
namespace FCRE
{
    /// <summary>
    ///MYSQLHelper 的摘要说明
    ///dgv.DataSource=MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, "select * from wp_posts", null).Tables[0].DefaultView;
    /////查询sql
    //String sqlSearch = "select * from student";
    //插入sql
    //String sqlInsert = "insert into student values (12,'张三',25,'大专')";
    //修改sql
    //String sqlUpdate = "update student set name='李四' where id= 3";
    //删除sql
    //String sqlDel = "delete from student where id = 12";
    /// </summary>
    public class SQLiteHelper
    {
        private SQLiteConnection connection = null;
        public static string sqliteconn = "Data Source=" + PubVar.uctepath + @"\db\ucte1.db3;Version=3;Password=;Pooling=true;FailIfMissing=false";
        public static bool conn_isok()
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection(sqliteconn);
                conn.Open();
                conn.Close();
                conn = null;
            }
            catch (Exception e)
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 生成SQLite连接字符串
        /// </summary>
        public static string GetConnectionString(string path)
        {
            return GetConnectionString(path, null);
        }
        public static string GetConnectionString(string path, string password)
        {
            if (string.IsNullOrEmpty(password))
                return "Data Source=" + path;
            return "Data Source=" + path + ";Password=" + password;
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <paramname="path"></param>
        /// <param name="newPassword"></param>
        /// <param name="oldPassword">没有密码时为空</param>
        public static bool ChangePassword(string path, string newPassword, string oldPassword = null)
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(GetConnectionString(path, oldPassword));
                con.Open();
                con.ChangePassword(newPassword);
                con.Close();
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        //----创建连接串并连接数据库----
        public SQLiteHelper(string path, string password)
        {
            string conn_str = "data source=" + path + ";password=" + password;
            connection = new SQLiteConnection(conn_str);
            connection.Open();
        }

        //----修改数据库密码----
        public bool ChangePassword(string newPassword)
        {
            bool ret = false;
            try
            {
                connection.ChangePassword(newPassword);
                ret = true;
            }
            catch (System.Data.SQLite.SQLiteException ex)
            {
                //log.Error("ChangeDBPwd occurs exceptions：" + ex.Message);
            }
            return ret;
        }

        //----关闭数据库连接----
        public void CloseConnection()
        {
            connection.Close();
            connection = null;
        }

        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public System.Data.DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            try
            {
                using (SQLiteCommand Command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        Command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(Command);
                    System.Data.DataTable dataTable = new System.Data.DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
            catch (SQLiteException ex)
            {
                System.Exception exc = ex;
                throw (exc);
            }
        }

        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int affectRows = 0;

            try
            {
                using (SQLiteTransaction Transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand Command = new SQLiteCommand(sql, connection, Transaction))
                    {
                        if (parameters != null)
                        {
                            Command.Parameters.AddRange(parameters);
                        }
                        affectRows = Command.ExecuteNonQuery();
                    }
                    Transaction.Commit();
                }
            }
            catch (SQLiteException ex)
            {
                affectRows = -1;
                //log.Error("ExecuteNonQuery occurs exception：" + ex.Message);
            }
            return affectRows;
        }
        /// <summary>
        /// 用执行的数据库连接执行一个返回数据集的sql命令
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="sql">存储过程名称或者sql命令语句</param>
        /// <returns>包含结果的读取器</returns>
        public static SQLiteDataReader ExecuteReader(string connectionString, string sql)
        {
            SQLiteConnection conn = new SQLiteConnection(connectionString);//创建数据库实例，指定文件位置
            conn.Open();

            //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
            //因此commandBehaviour.CloseConnection 就不会执行
            try
            {
                SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
                SQLiteDataReader reader = cmdQ.ExecuteReader();
                return reader;
            }
            catch
            {
                //关闭连接，抛出异常
                conn.Close();
                conn = null;
                throw;
            }
        }

    }
}

